package CCRMServer.Controllers;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import CCRMClient.ClientStart;
import CCRMServer.DBConnect;
import Common.Entities.*;
import Common.ServerAnswer;
import Common.SERVER_COMMANDS;

import java.util.Date;
import java.util.concurrent.TimeUnit;
import java.text.Format;
import java.text.DateFormat;
import java.text.SimpleDateFormat;


/**
 * This class implement server side action regarding customers
 * @author ori ziv
 * @author Oz Mouchtar
 * @author Ola
 *
 */
public class ServerCustomerManagement {
	
	
	
	/**
	 * This method query Customers table for customers according to a given condition.
	 * Condition must be supplied in a correct SQL syntax!!! 
	 * For example: condition = "customerName LIKE 'ori' OR customerPhone LIKE '%123%'"
	 * If condition string is empty then the query will be set to: "SELECT * FROM Customers"
	 * 
	 * @param condition
	 * @return an ArrayList of customers represents the search results.
	 * If search didn't return any results, an empty ArrayList is returned 
	 */
	public static ResultSet searchCustomerbyCondition (String condition) {
		String fields = "customerID, customerName, customerBrithday, customerPhone, customerAddress, customerStatus, regionID";
		String filter = "customerID = '" + condition + "' OR " +
						"customerName LIKE '%" + condition + "%' OR " +
						"customerPhone = '" + condition + "' OR customerAddress LIKE '%"  + condition +"%' OR " +
						"customerStatus ='" + condition +"' OR " + "regionID ='" + condition + "'";
		
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, "customers", fields, filter);
			return rs;
		} catch (SQLException e) {
			return null;
		}
		
	}

	/**
	 * this method query gives all regions list if condition string are empty
	 * @param condition
	 * @return
	 */
	public static ResultSet searchRegion(String condition){
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, "regions", null, null);
			return rs;
		} catch (SQLException e) {
			return null;
		}
	}
	
	/**
	 * This method updates a record in Customers table in the DB
	 * @param customer - this object contain data to update the data record with.
	 * @return Server Answer with message from server
	 * 
	 * @author ori ziv
	 * @author Ola
	 */
	public static ServerAnswer updateCustomerDetails (Customer customer) {//in work process
		String table = "regions";
		String filter = "regionID = '" + Integer.toString(customer.getRegionID()) + "'";
		String field = "regionID";
		Connection conDB = DBConnect.mySQLConnection();
		try{
			ResultSet rs = DBConnect.selectWithFilter(conDB, table, field, filter);
			if (!rs.next())
				return new ServerAnswer(SERVER_COMMANDS.UPDATE_CUSTOMER, null, "Region does not exist exists");
			rs.close();
			
			filter="CustomerID = '" + customer.getID() + "'";
			rs = DBConnect.selectWithFilter(conDB, "customers", null, filter);
			if (!rs.next())
				return new ServerAnswer(SERVER_COMMANDS.UPDATE_CUSTOMER, null, "Customer not exist exists");
			
			String[] fields ={"customerID","customerName","customerBrithday","customerPhone","customerAddress","customerStatus","regionID"};
			String[] values ={rs.getString(1),customer.getName(),rs.getDate(3).toString(),customer.getPhone(),customer.getCustomerAddress(),Integer.toString(customer.getCustomerStatus()),Integer.toString(customer.getRegionID())};
			String[] keyName = {"customerID"};
			String[] keyVal = {customer.getID()};
			boolean isOK = DBConnect.updateSingleRecord(conDB, "customers", fields, values,keyName,keyVal);
			if (isOK)
				return new ServerAnswer(SERVER_COMMANDS.UPDATE_CUSTOMER, null, "Update succeeded");
			
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.UPDATE_CUSTOMER, null, "DB Problem");
		}
		return null;
			
		

	}
	/**
	 * this  method  updates customer status in the DB
	 * @param customerId
	 * @return ServerAnswer with message from server
	 * @author Ola
	 */
	public static ServerAnswer updateCustomerStatus(String customerId){
		String filter= "CustomerID = '" + customerId + "'";
		Connection conDB = DBConnect.mySQLConnection();
		//check if customer exist
		try{
			ResultSet rs = DBConnect.selectWithFilter(conDB, "customers", null, filter);
			if (!rs.next())
				return new ServerAnswer(SERVER_COMMANDS.UPDATE_CUSTOMER_STATUS, null, "Customer not exist exists");
			else 
			{   
				//check current status of customer
				String[] fields ={"customerStatus"};
				 int status= rs.getInt("customerStatus");
			    String[] values ={Integer.toString((status+1)%2)};
			    String[] keyName = {"customerID"};
				String[] keyVal = {customerId};
				boolean isOK = DBConnect.updateSingleRecord(conDB, "customers", fields, values,keyName,keyVal);
				if (isOK)
					return new ServerAnswer(SERVER_COMMANDS.UPDATE_CUSTOMER_STATUS, null, "Update succeeded");
			}
		} catch (SQLException e){
		return new ServerAnswer(SERVER_COMMANDS.UPDATE_CUSTOMER_STATUS, null, "DB Problem");
		}
		
		return null;
		
	}
	
	/**
	 * This methods adds new customer to the DB
	 * @param added
	 * @return
	 */
	public static ServerAnswer addCustomer (Customer added) {
		String filter="CustomerID = '" + added.getID() + "'";
		String filter1="regionID = '" + added.getRegionID()+"'";
		Connection conDB = DBConnect.mySQLConnection();
		try{
			ResultSet rs1= DBConnect.selectWithFilter(conDB, "regions", null, filter1);
			if(!rs1.next())
				return  new ServerAnswer(SERVER_COMMANDS.ADD_CUSTOMER, null, "Region not exists");
		}catch(SQLException e){
			return new ServerAnswer(SERVER_COMMANDS.ADD_CUSTOMER, null, "Region not exist");
		}
		try{
			ResultSet rs = DBConnect.selectWithFilter(conDB, "customers", null, filter);
			if (rs.next())
				return new ServerAnswer(SERVER_COMMANDS.ADD_CUSTOMER, null, "Customer already exists");
			
			String[] fields ={"customerID","customerName","customerBrithday","customerPhone","customerAddress","customerStatus","regionID"};
			String[] values ={added.getID(),added.getName(),added.getStringBithday(),added.getPhone(),added.getCustomerAddress(),Integer.toString(added.getCustomerStatus()),Integer.toString(added.getRegionID())};
			
			boolean isOK = DBConnect.insertSingleRecord(conDB, "customers", fields, values);
			if (isOK)
				return new ServerAnswer(SERVER_COMMANDS.ADD_CUSTOMER, null, "Add succeeded");
			return null;
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ADD_CUSTOMER, null, "DB Problem");
		}
		
		
	}
	/**
	 * this method adds new region to the DB
	 * @param region
	 * @return ServerAnswer with message from server
	 * @author Ola
	 */
	public static ServerAnswer addRegion(Regions region){
		String filter="regionID = '" + region.getRegionID() +"'";
		Connection conDB = DBConnect.mySQLConnection();
		try{
			ResultSet rs = DBConnect.selectWithFilter(conDB, "regions", null, filter);
			if(rs.next())
				return new ServerAnswer(SERVER_COMMANDS.ADD_REGION, null, "Region already exists");
			String[] fields ={"regionID","regionName","regionDescription"};
			String[] values ={region.getRegionID().toString(),region.getRegionName(),region.getRegionDescription()};
			boolean isOK = DBConnect.insertSingleRecord(conDB, "regions", fields, values);
			if (isOK)
			return new ServerAnswer(SERVER_COMMANDS.ADD_REGION, null, "Add succeeded");
			return null;
		}catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ADD_REGION, null, "DB Problem");
	}
	}
	/**
	 * This method returns an ArrayList of strings of customers that are in a given teplate
	 * @param templateID - the template's ID
	 * @return ArrayList of String[]
	 * @throws SQLException
	 */
	public static ArrayList<String[]> showCustomersInTemplate (int templateID) throws SQLException{
		Connection conDB = DBConnect.mySQLConnection();
		ResultSet res = null;
	
			res = DBConnect.selectWithFilter(conDB, "customers c, customersintemplates cin", "customerID, customerName", "cid.templateID = " + templateID + " AND c.customerID = cid.customerID");
				ArrayList<String[]> customers = new ArrayList<String[]>();
				String[] customer = new String[2];
				while(res.next()){
					customer[0] = res.getString(1);
					customer[1] = res.getString(2);
					customers.add(customer);
				}
				return customers;
				
	
		
	}
	/**
	 * This method add a new customer permission to the DB
	 * @param prm - permission to add
	 * @return - a server answer with action status message
	 */
	public static ServerAnswer registerPermission(Permission prm) {
		String table;
		String filter;
		String fields;
		Connection conDB = DBConnect.mySQLConnection();
		
		// check if customer exists and active
		table = "customers";
		filter = "customerID = '" + prm.getCustomerID() + "'";
		fields = "customerStatus";
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
			if (!rs.next())
				return new ServerAnswer(SERVER_COMMANDS.ADD_PERMISSION, null, "Customer does not exsits");
			if (rs.getInt(1) != 1)
				return new ServerAnswer(SERVER_COMMANDS.ADD_PERMISSION, null, "Customer is not active");
			rs.close();
			
		// check if category exists
			table = "catalogcategories";
			filter = "categoryName = '" + prm.getCategoryID() + "'";
			fields = "categoryName";
			rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
			if (!rs.next())
				return new ServerAnswer(SERVER_COMMANDS.ADD_PERMISSION, null, "Category does not exsits");
			rs.close();
			
		// check if permission already exists
			table = "categoriespermissions";
			fields = "permissionType";
			filter = "customerID = '" + prm.getCustomerID() +
					"' AND categoryName = '" + prm.getCategoryID() +
					"' AND permissionID = (select MAX(permissionID) from categoriespermissions WHERE customerID = '" + prm.getCustomerID() +
					"' AND categoryName = '" + prm.getCategoryID() + "')";
			
			rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
			if (rs.next()) {
				int prmTyp = rs.getInt(1);
				if (prmTyp == prm.getPermissionType())
					return new ServerAnswer(SERVER_COMMANDS.ADD_PERMISSION, null, "Permission was already set");
			}
			rs.close();
			
		// add permission
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date today = new Date();
			String permissionDate = sdf.format(today);
			String insFields[] = {"categoryName", "customerID", "permissionType", "permissionDate"};
			String insValues[] = {prm.getCategoryID(), prm.getCustomerID(), Integer.toString(prm.getPermissionType()), permissionDate};
			boolean isOK = DBConnect.insertSingleRecord(conDB, table, insFields, insValues);
			if (isOK)
				return new ServerAnswer(SERVER_COMMANDS.ADD_PERMISSION, null, "Action Succeeded");
			return new ServerAnswer(SERVER_COMMANDS.ADD_PERMISSION, null, "DB Problem");
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ADD_PERMISSION, null, "DB Problem");
		}
	}
		
	/**
	 * This method retrieve from the DB a list of customers assigned to a specific sale-man in a specific sale
	 * @param arr - String array with the following format: {saleID, EmployeeID}
	 * @return - server answer with the search result or status message
	 */
	public static ServerAnswer showCustomersForSalesmanInSale(String [] arr){
		Connection con = DBConnect.mySQLConnection();
		String saleID = arr[0];
		String employeeID = arr[1];
		String table = "customers C, saleTeamCustomers STC";
		String fields = "STC.SaleID, STC.customerID, C.customerName, C.customerPhone, C.customerAddress";
		String filter = "C.customerID = STC.customerID AND STC.employeeID = '" + employeeID + "' AND STC.SaleID = '" + saleID + "'";
		ArrayList<String[]> customers = new ArrayList<String[]>();
		String row[] = new String[5];

		try{
			ResultSet res = DBConnect.selectWithFilter(con, table, fields, filter);
			while (res.next()) {
				row[0] = res.getString(1);
				row[1] = res.getString(2);
				row[2] = res.getString(3);
				row[3] = res.getString(4);
				row[4] = res.getString(5);
				customers.add(row.clone());
			}
			if (customers.isEmpty())
				return new ServerAnswer(SERVER_COMMANDS.SHOW_CUSTOMERS_FOR_SALESMAN, null, "No customers assigned for you in this sale");
			return new ServerAnswer(SERVER_COMMANDS.SHOW_CUSTOMERS_FOR_SALESMAN, customers, "");
			
			}catch(SQLException e){
				return new ServerAnswer(SERVER_COMMANDS.SHOW_CUSTOMERS_FOR_SALESMAN, null, "DB Problem");
		}
	}
	
	
		
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	
